OpenEdge Data Management:<br />SQL Reference PREPARE

Parses and assigns a name to an ad hoc or dynamically generated SQL statement for execution. You use a PREPARE statement in a series of steps that allows a program to accept or generate SQL statements at run time.

Syntax

PREPARE statement_name FROM statement_string ;

A name for the dynamically generated statement. DESCRIBE, EXECUTE, and DECLARE CURSOR statements refer to this statement_name. A statement_name must be unique in a program.

Specifies the SQL statement to be prepared for dynamic execution. You can use either the name of a C language string variable containing the SQL statement, or you can specify the SQL statement as a quoted literal. If there is an SQL syntax error, the PREPARE statement returns an error in the SQLCA.

Syntax
{ :host_variable \| quoted_literal }

The first example is a code fragment from the DynUpd function in sample program 3DynUpd.pc, which illustrates dynamic processing of an UPDATE statement:

/* Process a dynamic non-SELECT input statement PREPARE the statement EXECUTE the prepared statement COMMIT WORK */ EXEC SQL PREPARE dynstmt FROM :sql_stmt_v ; EXEC SQL EXECUTE dynstmt ; EXEC SQL COMMIT WORK ;

This example is a code fragment from the DynSel function in sample program 4DynSel.pc, which illustrates dynamic processing of a SELECT statement:

/* PREPARE a the dynamic SELECT statement. DECLARE cursor for the prepared SELECT statement. NOTE: You must set input parameter values before OPEN CURSOR. If your query has input parameters, you must define them in the DECLARE SECTION. OPEN the declared cursor. NOTE: For static statements, if a DECLARE CURSOR statement contains references to automatic variables, the OPEN CURSOR statement must be in the same C function. Name WHENEVER routine for NOT FOUND condition. FETCH a row and print results until no more rows. */ EXEC SQL PREPARE stmtid from :sel_stmt_v ; EXEC SQL DECLARE dyncur CURSOR FOR stmtid ; EXEC SQL OPEN dyncur ; EXEC SQL WHENEVER NOT FOUND GOTO seldone ;

/* 
**    PREPARE a the dynamic SELECT statement. 
**    DECLARE cursor for the prepared SELECT statement. 
**    NOTE: You must set input parameter values before OPEN CURSOR. 
**    If your query has input parameters, you must define them in 
**    the DECLARE SECTION. 
**    OPEN the declared cursor. 
**    NOTE: For static statements, if a DECLARE CURSOR 
**    statement contains references to automatic variables, 
**    the OPEN CURSOR statement must be in the same C function. 
** 
**    Name WHENEVER routine for NOT FOUND condition. 
**    FETCH a row and print results until no more rows. 
*/ 
EXEC SQL PREPARE stmtid from :sel_stmt_v ; 
EXEC SQL DECLARE dyncur CURSOR FOR stmtid ; 
EXEC SQL OPEN dyncur ;             
EXEC SQL WHENEVER NOT FOUND GOTO seldone ;

Notes

A statement string can have one or more references to input variables. These variables represent values supplied at run time to:

INSERT and UPDATE statements.

Predicates in DELETE, UPDATE, and SELECT statements.

A program supplies an input variable to a PREPARE statement either as a substitution name or as a parameter marker:

A substitution name is a name preceded by a colon ( : ) in a statement string. This name does not refer to a C language variable, but acts only as a placeholder for input variables.

A parameter marker is a question mark ( ? ) in the statement string, serving as a placeholder for input variables.

The USING clauses of EXECUTE and OPEN statements identify host language storage. The values in this storage expand a statement string, replacing a substitution name or a parameter marker. You can design your program to execute the same prepared statement many times in a transaction, supplying different values for input variables for each execution. If you COMMIT or ROLLBACK the transaction, you must PREPARE the statement string again.

PREPARE

Syntax

Notes

Authorization

Related statements